﻿using Com.Lancetinc.Evs.Helpers;
using NLog;
using System;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Reflection;

namespace Com.Lancetinc.Evs.Data
{
    public class SqlDataAccessor
    {
        public readonly string DataPath;
        private string _connectionString;
        private NotCloseDbConnection _conn = null;

        private static readonly Logger logger = NLog.LogManager.GetCurrentClassLogger();

        /// <summary>
        /// 创建数据库访问对象
        /// </summary>
        /// <param name="dbPath">数据文件路径</param>
        /// <param name="password">数据库密码</param>
        private SqlDataAccessor(string dbPath, string password = null)
        {
            this.DataPath = dbPath;
            InitializeConnectionString(password);
        }

        private void InitializeConnectionString(string password)
        {
            if (string.IsNullOrEmpty(password))
            {
                this._connectionString = string.Format(@"Data Source={0};FailIfMissing=True", DataPath);
            }
            else
            {
                this._connectionString = string.Format(@"Data Source={0};Password={1};FailIfMissing=True", DataPath, password);
            }
        }

        public void ChangePassword(string newPassword)
        {
            using (var conn = new SQLiteConnection(this._connectionString))
            {
                conn.Open();
                conn.ChangePassword(newPassword);
            }
            InitializeConnectionString(newPassword);
        }

        public NotCloseDbConnection GetConnection()
        {
            if (_conn == null)
            {
                _conn = new NotCloseDbConnection(NewConnection());
            }
            return _conn;
        }

        public DbConnection NewConnection()
        {
            return NewConnection(this._connectionString);
        }

        public void ForceClose()
        {
            if (_conn != null)
            {
                _conn.ForceClose();
                _conn = null;
            }
        }

        private static SqlDataAccessor _dataAccessor;

        private static DbConnection NewConnection(string connectionString)
        {
            var conn = new SQLiteConnection(connectionString);
            conn.Open();
            return conn;
        }

        private static void CreateTables(DbCommand cmd)
        {
            //T_EXPER(SeqNo, MedicalRecordNo, BedNo, Name, Sex, Age, AttendingDoctor, Mediastinus, Notes, StartTime, EndTime)
            cmd.CommandText = "create table Exper(" +
                "SeqNo TEXT not null," +
                "MedicalRecordNo TEXT, " +
                "BedNo TEXT, " +
                "Name TEXT," +
                "Sex TEXT," +
                "Age INTEGER," +
                "AttendingDoctor TEXT," +
                "Mediastinus TEXT," +
                "Notes TEXT," +
                "StartTime TEXT," +
                "EndTime TEXT," +
                "Locked BOOLEAN," +
                "VideoRotationAngle INTEGER," +
                "VideoWidth INTEGER," +
                "VideoHeight INTEGER," +
                "MediaDuration LONG," +
                "HcSerialNo TEXT," +
                "FlipHorizontally int DEFAULT 0," +
                "FlipVertically int DEFAULT 0," +
                "Diagnosis TEXT," +
                "Identity TEXT," +
                "InspectionPosition TEXT," +
                "PrintCount int DEFAULT 0," +
                "LastPrintTime TEXT," +
                "ReportImages TEXT," +
                "primary key(SeqNo))";
            cmd.ExecuteNonQuery();

            // 配置表
            CreateConfigTable(cmd);
        }

        public static void Initialize(string dbSetting)
        {
            string[] ss = dbSetting.Split(',');
            string dbPath = Path.GetFullPath(ss[0]);
            string password = null;
            if (ss.Length == 2)
            {
                password = ss[1];
                password = CipherUtils.Decrypt(password, "levs" + 201902);
            }
            bool upgradeCheck = true;
            if (!File.Exists(dbPath))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(dbPath));
                var updatePath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "UpdateFiles");
                updatePath = Path.Combine(updatePath, Path.GetFileName(dbPath));
                if (File.Exists(updatePath))
                {
                    File.Move(updatePath, dbPath);
                    File.SetAttributes(dbPath, FileAttributes.Archive);
                }
                else
                {
                    SQLiteConnection.CreateFile(dbPath);
                    try
                    {
                        using (SQLiteConnection conn = (SQLiteConnection)NewConnection(string.Format(@"Data Source={0};FailIfMissing=True", dbPath)))
                        {
                            if (!string.IsNullOrEmpty(password))
                            {
                                conn.ChangePassword(password);
                            }
                            using (DbCommand cmd = conn.CreateCommand())
                            {
                                CreateTables(cmd);
                            }
                        }
                        upgradeCheck = false;
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex);
                        System.IO.File.Delete(dbPath);
                    }
                }
            }
            _dataAccessor = new SqlDataAccessor(dbPath, password);
            if (upgradeCheck)
            {
                UpgradeDatabaseIfNeeded(_dataAccessor);
            }
        }

        public static SqlDataAccessor Default => _dataAccessor;

        private static void UpgradeDatabaseIfNeeded(SqlDataAccessor dataAccessor)
        {
            using var conn = dataAccessor.NewConnection();
            // ALTER TABLE Exper ADD COLUMN MediaDuration LONG 
            using DbCommand cmd = conn.CreateCommand();
            TryAddMediaDurationColumn(cmd);
            TryUpgradeV2(cmd);
            TryUpgradeV3(cmd);
        }

        private static void TryAddMediaDurationColumn(DbCommand cmd)
        {
            cmd.CommandText = "select MediaDuration from Exper limit 1";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("MediaDuration") > -1)
                {
                    cmd.CommandText = "ALTER TABLE Exper ADD COLUMN MediaDuration LONG";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #region V2

        /// <summary>
        /// 2022/07/04 增加翻转设置
        /// </summary>
        private static void TryUpgradeV2(DbCommand cmd)
        {
            cmd.CommandText = "select HcSerialNo from Exper limit 1";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("HcSerialNo") > -1)
                {
                    UpgradeV2Core(cmd);
                }
            }
        }

        private static void UpgradeV2Core(DbCommand cmd)
        {
            var sqlList = new string[]
            {
                "ALTER TABLE Exper ADD COLUMN HcSerialNo TEXT",
                "ALTER TABLE Exper ADD COLUMN FlipHorizontally int DEFAULT 0",
                "ALTER TABLE Exper ADD COLUMN FlipVertically int DEFAULT 0"
            };
            foreach (var sql in sqlList)
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region V3

        /// <summary>
        /// 2023/04/07
        /// 增加诊断、身份证号、检查部位
        /// </summary>
        /// <param name="cmd"></param>
        private static void TryUpgradeV3(DbCommand cmd)
        {
            cmd.CommandText = "select Diagnosis from Exper limit 1";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("Diagnosis") > -1)
                {
                    UpgradeV3Core(cmd);
                }
            }
        }

        private static void UpgradeV3Core(DbCommand cmd)
        {
            var sqlList = new[]
            {
                "ALTER TABLE Exper ADD COLUMN Diagnosis TEXT",
                "ALTER TABLE Exper ADD COLUMN Identity TEXT",
                "ALTER TABLE Exper ADD COLUMN InspectionPosition TEXT",
                "ALTER TABLE Exper ADD COLUMN PrintCount int DEFAULT 0",
                "ALTER TABLE Exper ADD COLUMN LastPrintTime TEXT",
                "ALTER TABLE Exper ADD COLUMN ReportImages TEXT"
            };
            foreach (var sql in sqlList)
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }

            CreateConfigTable(cmd);
        }

        private static void CreateConfigTable(DbCommand cmd)
        {
            cmd.CommandText = @"CREATE TABLE IF NOT EXISTS TblConfig(
                              Name TEXT not null,
                              Value TEXT, 
                              Comment TEXT, 
                              primary key(Name))";
            cmd.ExecuteNonQuery();

            cmd.CommandText =
                "INSERT INTO \"TblConfig\"(\"Name\", \"Value\", \"Comment\") VALUES ('db_version', '1', '1:初始版本')";
            cmd.ExecuteNonQuery();
        }
        #endregion

    }
}
